--    Program: 		CAPARIMP_CUSTOMER_SITE_USE.sql
--    Description:  Import AR CUSTOMER SITE USE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_site_use_id   	        number;
                    		                 		
   cursor cur_ar_customer_site_use is
      select * from CRST_HZ_CUST_SITE_USES_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_site_use loop   	
     l_err_msg_tmp := '';
     
     SELECT HZ_CUST_SITE_USES_S.NEXTVAL 
     INTO l_site_use_id
     FROM DUAL;
     
     update CRST_HZ_CUST_SITE_USES_ALL
     set    site_use_id_n = l_site_use_id
     where  site_use_id = cur.site_use_id;
     
     begin
       -- import data 
       insert into HZ_CUST_SITE_USES_ALL
       (
          SITE_USE_ID                     ,
          CUST_ACCT_SITE_ID               ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATED_BY                 ,
          CREATION_DATE                   ,
          CREATED_BY                      ,
          SITE_USE_CODE                   ,
          PRIMARY_FLAG                    ,
          STATUS                          ,
          LOCATION                        ,
          LAST_UPDATE_LOGIN               ,
          CONTACT_ID                      ,
          BILL_TO_SITE_USE_ID             ,
          ORIG_SYSTEM_REFERENCE           ,
          SIC_CODE                        ,
          PAYMENT_TERM_ID                 ,
          GSA_INDICATOR                   ,
          SHIP_PARTIAL                    ,
          SHIP_VIA                        ,
          FOB_POINT                       ,
          ORDER_TYPE_ID                   ,
          PRICE_LIST_ID                   ,
          FREIGHT_TERM                    ,
          WAREHOUSE_ID                    ,
          TERRITORY_ID                    ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          REQUEST_ID                      ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          TAX_REFERENCE                   ,
          SORT_PRIORITY                   ,
          TAX_CODE                        ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          ATTRIBUTE16                     ,
          ATTRIBUTE17                     ,
          ATTRIBUTE18                     ,
          ATTRIBUTE19                     ,
          ATTRIBUTE20                     ,
          ATTRIBUTE21                     ,
          ATTRIBUTE22                     ,
          ATTRIBUTE23                     ,
          ATTRIBUTE24                     ,
          ATTRIBUTE25                     ,
          LAST_ACCRUE_CHARGE_DATE         ,
          SECOND_LAST_ACCRUE_CHARGE_DATE  ,
          LAST_UNACCRUE_CHARGE_DATE       ,
          SECOND_LAST_UNACCRUE_CHRG_DATE  ,
          DEMAND_CLASS_CODE               ,
          ORG_ID                          ,
          TAX_HEADER_LEVEL_FLAG           ,
          TAX_ROUNDING_RULE               ,
          WH_UPDATE_DATE                  ,
          GLOBAL_ATTRIBUTE1               ,
          GLOBAL_ATTRIBUTE2               ,
          GLOBAL_ATTRIBUTE3               ,
          GLOBAL_ATTRIBUTE4               ,
          GLOBAL_ATTRIBUTE5               ,
          GLOBAL_ATTRIBUTE6               ,
          GLOBAL_ATTRIBUTE7               ,
          GLOBAL_ATTRIBUTE8               ,
          GLOBAL_ATTRIBUTE9               ,
          GLOBAL_ATTRIBUTE10              ,
          GLOBAL_ATTRIBUTE11              ,
          GLOBAL_ATTRIBUTE12              ,
          GLOBAL_ATTRIBUTE13              ,
          GLOBAL_ATTRIBUTE14              ,
          GLOBAL_ATTRIBUTE15              ,
          GLOBAL_ATTRIBUTE16              ,
          GLOBAL_ATTRIBUTE17              ,
          GLOBAL_ATTRIBUTE18              ,
          GLOBAL_ATTRIBUTE19              ,
          GLOBAL_ATTRIBUTE20              ,
          GLOBAL_ATTRIBUTE_CATEGORY       ,
          PRIMARY_SALESREP_ID             ,
          FINCHRG_RECEIVABLES_TRX_ID      ,
          DATES_NEGATIVE_TOLERANCE        ,
          DATES_POSITIVE_TOLERANCE        ,
          DATE_TYPE_PREFERENCE            ,
          OVER_SHIPMENT_TOLERANCE         ,
          UNDER_SHIPMENT_TOLERANCE        ,
          ITEM_CROSS_REF_PREF             ,
          OVER_RETURN_TOLERANCE           ,
          UNDER_RETURN_TOLERANCE          ,
          SHIP_SETS_INCLUDE_LINES_FLAG    ,
          ARRIVALSETS_INCLUDE_LINES_FLAG  ,
          SCHED_DATE_PUSH_FLAG            ,
          INVOICE_QUANTITY_RULE           ,
          PRICING_EVENT                   ,
          GL_ID_REC                       ,
          GL_ID_REV                       ,
          GL_ID_TAX                       ,
          GL_ID_FREIGHT                   ,
          GL_ID_CLEARING                  ,
          GL_ID_UNBILLED                  ,
          GL_ID_UNEARNED                  ,
          GL_ID_UNPAID_REC                ,
          GL_ID_REMITTANCE                ,
          GL_ID_FACTOR                    ,
          TAX_CLASSIFICATION              ,
          OBJECT_VERSION_NUMBER           ,
          CREATED_BY_MODULE               ,
          APPLICATION_ID                   
       )
       values
       (
            l_site_use_id                       ,
            CAPARIMP_MAPPING.GetHZCustAcctSiteIDMapping(cur.CUST_ACCT_SITE_ID),
            cur.LAST_UPDATE_DATE                ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE                   ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.SITE_USE_CODE                   ,
            cur.PRIMARY_FLAG                    ,
            cur.STATUS                          ,
            l_site_use_id,
            cur.LAST_UPDATE_LOGIN               ,
            cur.CONTACT_ID                      , --NULL
            CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.BILL_TO_SITE_USE_ID),
            cur.ORIG_SYSTEM_REFERENCE           ,
            cur.SIC_CODE                        ,
            cur.PAYMENT_TERM_ID                 ,
            cur.GSA_INDICATOR                   ,
            cur.SHIP_PARTIAL                    ,
            cur.SHIP_VIA                        ,
            cur.FOB_POINT                       ,
            cur.ORDER_TYPE_ID                   ,
            cur.PRICE_LIST_ID                   ,
            cur.FREIGHT_TERM                    ,
            cur.WAREHOUSE_ID                    ,
            cur.TERRITORY_ID                    ,
            cur.ATTRIBUTE_CATEGORY              ,
            cur.ATTRIBUTE1                      ,
            cur.ATTRIBUTE2                      ,
            cur.ATTRIBUTE3                      ,
            cur.ATTRIBUTE4                      ,
            cur.ATTRIBUTE5                      ,
            cur.ATTRIBUTE6                      ,
            cur.ATTRIBUTE7                      ,
            cur.ATTRIBUTE8                      ,
            cur.ATTRIBUTE9                      ,
            cur.ATTRIBUTE10                     ,
            cur.REQUEST_ID                      ,
            cur.PROGRAM_APPLICATION_ID          ,
            cur.PROGRAM_ID                      ,
            cur.PROGRAM_UPDATE_DATE             ,
            cur.TAX_REFERENCE                   ,
            cur.SORT_PRIORITY                   ,
            cur.TAX_CODE                        ,
            cur.ATTRIBUTE11                     ,
            cur.ATTRIBUTE12                     ,
            cur.ATTRIBUTE13                     ,
            cur.ATTRIBUTE14                     ,
            cur.ATTRIBUTE15                     ,
            cur.ATTRIBUTE16                     ,
            cur.ATTRIBUTE17                     ,
            cur.ATTRIBUTE18                     ,
            cur.ATTRIBUTE19                     ,
            cur.ATTRIBUTE20                     ,
            cur.ATTRIBUTE21                     ,
            cur.ATTRIBUTE22                     ,
            cur.ATTRIBUTE23                     ,
            cur.ATTRIBUTE24                     ,
            cur.ATTRIBUTE25                     ,
            cur.LAST_ACCRUE_CHARGE_DATE         ,
            cur.SECOND_LAST_ACCRUE_CHARGE_DATE  ,
            cur.LAST_UNACCRUE_CHARGE_DATE       ,
            cur.SECOND_LAST_UNACCRUE_CHRG_DATE  ,
            cur.DEMAND_CLASS_CODE               ,
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.TAX_HEADER_LEVEL_FLAG           ,
            cur.TAX_ROUNDING_RULE               ,
            cur.WH_UPDATE_DATE                  ,
            cur.GLOBAL_ATTRIBUTE1               ,
            cur.GLOBAL_ATTRIBUTE2               ,
            cur.GLOBAL_ATTRIBUTE3               ,
            cur.GLOBAL_ATTRIBUTE4               ,
            cur.GLOBAL_ATTRIBUTE5               ,
            cur.GLOBAL_ATTRIBUTE6               ,
            cur.GLOBAL_ATTRIBUTE7               ,
            cur.GLOBAL_ATTRIBUTE8               ,
            cur.GLOBAL_ATTRIBUTE9               ,
            cur.GLOBAL_ATTRIBUTE10              ,
            cur.GLOBAL_ATTRIBUTE11              ,
            cur.GLOBAL_ATTRIBUTE12              ,
            cur.GLOBAL_ATTRIBUTE13              ,
            cur.GLOBAL_ATTRIBUTE14              ,
            cur.GLOBAL_ATTRIBUTE15              ,
            cur.GLOBAL_ATTRIBUTE16              ,
            cur.GLOBAL_ATTRIBUTE17              ,
            cur.GLOBAL_ATTRIBUTE18              ,
            cur.GLOBAL_ATTRIBUTE19              ,
            cur.GLOBAL_ATTRIBUTE20              ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
            cur.PRIMARY_SALESREP_ID             ,
            cur.FINCHRG_RECEIVABLES_TRX_ID      ,
            cur.DATES_NEGATIVE_TOLERANCE        ,
            cur.DATES_POSITIVE_TOLERANCE        ,
            cur.DATE_TYPE_PREFERENCE            ,
            cur.OVER_SHIPMENT_TOLERANCE         ,
            cur.UNDER_SHIPMENT_TOLERANCE        ,
            cur.ITEM_CROSS_REF_PREF             ,
            cur.OVER_RETURN_TOLERANCE           ,
            cur.UNDER_RETURN_TOLERANCE          ,
            cur.SHIP_SETS_INCLUDE_LINES_FLAG    ,
            cur.ARRIVALSETS_INCLUDE_LINES_FLAG  ,
            cur.SCHED_DATE_PUSH_FLAG            ,
            cur.INVOICE_QUANTITY_RULE           ,
            cur.PRICING_EVENT                   ,
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REC),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REV),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_TAX),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_FREIGHT),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_CLEARING),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_UNBILLED),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_UNEARNED),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_UNPAID_REC),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_REMITTANCE),
            CAPBRIMP_MAPPING.GetCCIDMapping(cur.GL_ID_FACTOR),
            cur.TAX_CLASSIFICATION              ,
            cur.OBJECT_VERSION_NUMBER           ,
            cur.CREATED_BY_MODULE               ,
            cur.APPLICATION_ID                                                   
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;  
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_SITE_USE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
